﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelImport
{
    public class SQLHelper
    {
        /// <summary>
        /// 创建MySQL连接
        /// </summary>
        /// <returns></returns>
        public IDbConnection CreateSQLConn(string connkey = null)
        {
            return ContextFactory.CreateSQLConn(connkey);
        }
        /// <summary>
        /// 增、删、改同步操作
        /// </summary>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>操作结果 1成功 0失败</returns>
        public int ExcuteNonQuery(string cmd, DynamicParameters param, string connkey = null, bool flag = false)
        {
            int result = 0;
            using (var conn = ContextFactory.CreateSQLConn(connkey))
            {
                try
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    var trans = conn.BeginTransaction();
                    if (flag)
                    {
                        result = conn.Execute(cmd, param, trans, null, CommandType.StoredProcedure);
                    }
                    else
                    {
                        result = conn.Execute(cmd, param, trans, null, CommandType.Text);
                    }
                    if (result > 0)
                        trans.Commit();
                    else
                        trans.Rollback();
                }
                catch (Exception ce)
                {
                    throw ce;
                }
                finally
                {
                    conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 批量增、删、改同步操作
        /// </summary>
        /// <param name="cmd">sql语句</param>
        /// <param name="paramlist">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>操作结果 1成功 0失败</returns>
        public int ExcuteNonQuery(string cmd, List<DynamicParameters> paramlist, string connkey = null, bool flag = false)
        {
            int result = 0;
            using (var conn = ContextFactory.CreateSQLConn(connkey))
            {
                try
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();

                    var trans = conn.BeginTransaction();

                    if (flag)
                    {
                        result = conn.Execute(cmd, paramlist, trans, null, CommandType.StoredProcedure);
                    }
                    else
                    {
                        result = conn.Execute(cmd, paramlist, trans, null, CommandType.Text);
                    }

                    if (result > 0)
                        trans.Commit();
                    else
                        trans.Rollback();
                }
                catch (Exception ce)
                {
                    throw ce;
                }
                finally
                {
                    conn.Close();
                }

            }
            return result;
        }

        /// <summary>
        /// 同步查询单对象操作
        /// </summary>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>T</returns>
        public T FindObjectT<T>(string cmd, DynamicParameters param, string connkey = null, bool flag = false) where T : class, new()
        {
            T t = null;
            using (var conn = ContextFactory.CreateSQLConn(connkey))
            {
                try
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    t = conn.Query<T>(cmd, param).FirstOrDefault();
                }
                catch (Exception ce)
                {
                    throw ce;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return t;
        }

        /// <summary>
        /// 同步查询操作
        /// </summary>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>object</returns>
        public object FindObject(string cmd, DynamicParameters param, string connkey = null, bool flag = false)
        {
            object result = null;
            using (var conn = ContextFactory.CreateSQLConn(connkey))
            {
                try
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();

                    if (flag)
                    {
                        result = conn.ExecuteScalar(cmd, param, null, null, CommandType.StoredProcedure);
                    }
                    else
                    {
                        result = conn.ExecuteScalar(cmd, param, null, null, CommandType.Text);
                    }
                }
                catch (Exception ce)
                {
                    throw ce;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return result;
        }

        /// <summary>
        /// 同步查询数据集合
        /// </summary>
        /// <param name="connection">连接数据库</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public List<T> FindList<T>(string cmd, DynamicParameters param, string connkey = null, bool flag = false) where T : class, new()
        {
            List<T> t = null;
            using (var conn = ContextFactory.CreateSQLConn(connkey))
            {
                try
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    t = conn.Query<T>(cmd, param).AsList().ToList();
                }
                catch (Exception ce)
                {
                    throw ce;
                }
                finally
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
            return t;
        }
        /// <summary>
        /// 查询返回DataTable
        /// </summary>
        /// <param name="connection">连接数据库</param>
        /// <param name="cmd">sql语句</param>
        /// <param name="param">参数</param>
        /// <param name="flag">true存储过程，false sql语句</param>
        /// <returns>t</returns>
        public DataTable FindDataTable(string cmd, DynamicParameters param, string connkey = null, bool flag = false)
        {
            DataTable dt = new DataTable();
            IDataReader reader = null;


            using (var conn = ContextFactory.CreateSQLConn(connkey))
            {
                try
                {
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    if (flag)
                    {
                        reader = conn.ExecuteReader(cmd, param, null, 3600, CommandType.StoredProcedure);
                    }
                    else
                    {
                        reader = conn.ExecuteReader(cmd, param, null, 3600, CommandType.Text);
                    }
                    dt.Load(reader);
                }
                catch (Exception ce)
                {
                    throw ce;
                }
                finally
                {
                    if (reader != null)
                    {
                        reader.Close();
                        reader.Dispose();
                    }
                    conn.Close();
                    conn.Dispose();
                }
            }


            return dt;
        }
    }
}
